const fs = require('fs')
var sqlite3 = require('sqlite3').verbose()




var tree_with_list = {
    //列表转树结构
    list2tree:function(in_list,out_tree,parent_field,first_parent_value,list_pkid_field,tree_child_field){
        var find = function (list,pid) {
            var tmp = []
            for(var i=0;i<list.length;i++){
                if(list[i][parent_field]==pid){
                    tmp.push(list[i])
                }
            }
            return tmp
        }
        var deep = function(list,tree,pid){
            var tmp_list = find(list,pid)
            for(var i=0;i<tmp_list.length;i++){
                tree.push(tmp_list[i])
                var list2 = find(list,tmp_list[i][list_pkid_field])
                if(list2.length>0){
                    tmp_list[i][tree_child_field]=[]
                    deep(list,tmp_list[i][tree_child_field],tmp_list[i][list_pkid_field])
                }
            }
        }
        deep(in_list,out_tree,first_parent_value)
    },
    //树结构转列表
    tree2list:function(in_tree,out_list,child_field){
        var deep = function(tree){
            for(var i=0;i<tree.length;i++){
                out_list.push(tree[i])
                if(tree[i][child_field] && tree[i][child_field].length){
                    deep(tree[i][child_field])
                    delete(tree[i][child_field])
                }
            }
        }
        deep(in_tree)
        return
    },
    listIntotree:function(in_tree,list,list_parent_field,tree_child_field,tree_pkid_field){
        var find = function (list,pid) {
            var tmp = []
            for(var i=0;i<list.length;i++){
                if(list[i][list_parent_field]==pid){
                    tmp.push(list[i])
                }
            }
            return tmp
        }
        var deep = function(tree){
            for(var i=0;i<tree.length;i++){
                var tmp = find(list,tree[i][tree_pkid_field])
                if(tree[i][tree_child_field] && tree[i][tree_child_field].length){
                    deep(tree[i][tree_child_field])
                }
                if(tmp.length>0){
                    if(!tree[i][tree_child_field]){
                        tree[i][tree_child_field]=[]
                    }
                    if(tree[i][tree_child_field]){
                        for(var j=0;j<tmp.length;j++){
                            tree[i][tree_child_field].push(tmp[j])
                        }
                    }
                }
            }
        }
        deep(in_tree)
        return
    }
}



//parent_id =1 是一级的notebook
class NodeBook {
    file_path
    db
    constructor(file_name,complete_call){
        var ok = fs.existsSync(file_name)
        this.db = new sqlite3.Database(file_name)
        this.file_path=file_name
        this.db.serialize(()=>{
            this.db.run(`
                CREATE TABLE IF NOT EXISTS note(
                    id VARCHAR(13) primary key NOT NULL,
                    notebook_id VARCHAR(13) NOT NULL,
                    name VARCHAR(30) NOT NULL,
                    text TEXT NOT NULL,
                    update_time INTEGER NOT NULL,
                    add_time INTEGER NOT NULL
                );
            `)
            this.db.run(`
                CREATE TABLE IF NOT EXISTS notebook(
                    id VARCHAR(13) primary key NOT NULL,
                    parent_id VARCHAR(13) NOT NULL,
                    name VARCHAR(30) NOT NULL,
                    update_time INTEGER NOT NULL,
                    add_time INTEGER NOT NULL
                );
            `,(r,err)=>{
                if(!err){
                    //创建索引
                    this.db.run(`
                        CREATE INDEX IF NOT EXISTS parent_id
                        ON notebook (parent_id);
                    `)
                    
                    this.notebook_set("1","0","全部",complete_call)
                }
            })
        })
    }
    all_search(keyword,callback=(tree)=>{}){
        var index =0 
        var l1,l2,l3,l4
        function next(l1,l3){
            if(index==2){
                var tree = []
                tree_with_list.list2tree(l1,tree,'parent_id',0,'id','children')
                tree_with_list.listIntotree(tree,l3,'notebook_id','children','id')
                callback(tree)
            }
        }
        this.notebook_list(null,(v1,v2)=>{
            index++
            l1=v1
            l2=v2
            next(l1,l3)
        }),
        this.note_list(null,keyword,(v3,v4)=>{
            index++
            l3=v3
            l4=v4
            next(l1,l3)
        })
    }
    all(callback=(tree)=>{}){
        var index =0 
        var l1,l2,l3,l4
        function next(l1,l3){
            if(index==2){
                var tree = []
                tree_with_list.list2tree(l1,tree,'parent_id',0,'id','children')
                tree_with_list.listIntotree(tree,l3,'notebook_id','children','id')
                callback(tree)
            }
        }
        this.notebook_list(null,(v1,v2)=>{
            index++
            l1=v1
            l2=v2
            next(l1,l3)
        }),
        this.note_list(null,null,(v3,v4)=>{
            index++
            l3=v3
            l4=v4
            next(l1,l3)
        })
    }
    notebook_list(keyword='',callback=(list,len)=>{}){
        //列表
        var list = []
        this.db.serialize(()=>{
            var where = ""
            if(keyword){
                var keywordcc = keyword.replace(/'/g,"\\'")
                where = `where name LIKE '%${keywordcc}%'`
            }
            this.db.each(`SELECT id,parent_id,name as label,'notebook' as type from notebook ${where} order by update_time DESC,add_time DESC`,(err,row)=>{
                if (err){
                    console.log("LIST读取错误",err)
                }
                list.push(row)
            },(err,c)=>{
                if (err){
                    console.log("LIST读取完成错误",err)
                }
                var len = c
                callback(list,len)
            })
        })
    }
    note_list(notebook_id,keyword,callback=(list,len)=>{}){
        //列表
        var list = []
        this.db.serialize(()=>{
            var where =""
            if(notebook_id){
                where+=`notebook_id = '${notebook_id}'`
            }
            if(keyword){
                var keywordcc = keyword
                if(keyword){
                    keywordcc = keyword.replace(/'/g,"\\'")
                }
                if(where!=""){
                    where+=" AND "
                }
                where+=` name like '%${keywordcc}%' OR text LIKE '%${keywordcc}%' `
            }
            if(where!=""){
                where=" WHERE " + where
            }

            this.db.each(`SELECT id,notebook_id,name as label,'' as text,'note' as type from note ${where} order by update_time DESC,add_time DESC`,(err,row)=>{
                if (err){
                    console.log("LIST读取错误",err)
                }
                list.push(row)
            },(err,c)=>{
                if (err){
                    console.log("LIST读取完成错误",err)
                }
                var len = c
                callback(list,len)
            })
        })
    }
    note_get(note_id,callback=(row,err)=>{}){
        this.db.serialize(()=>{
            this.db.get("SELECT id,notebook_id,text FROM note WHERE id ='"+note_id+"'",(err,row)=>{
                callback(row,err)
            })
        })
    }
    notebook_set(notebook_id,parent_id,name,callback=(err)=>{}){
        var now = Date.now()
        this.db.serialize(()=>{
            this.db.get("SELECT id FROM notebook WHERE id ='"+notebook_id+"'",(err,row)=>{
                if(!row){
                    this.db.run(`INSERT INTO notebook(id,parent_id,name,update_time,add_time)values('${notebook_id}','${parent_id}','${name}','${now}','${now}')`,(err)=>{
                        if(err){
                            console.log('无法插入内容',err)
                        }
                        callback(err)
                    })
                }else{
                    this.db.run(`UPDATE notebook SET name='${name}',parent_id='${parent_id}',update_time='${now}' where id='${notebook_id}'`,(err)=>{
                        if(err){
                            console.log('无法更新内容',err)
                        }
                        callback(err)
                    })
                }
            })
        })
    }
    notebook_rem(notebook_id,callback=(err)=>{}){
        this.db.serialize(()=>{
            this.db.run("DELETE FROM notebook WHERE id = '"+notebook_id+"'",callback)
        })
    }
    note_set_text(note_id,text,callback=(err)=>{}){
        this.db.serialize(()=>{
            this.db.get("SELECT id FROM note WHERE id ='"+note_id+"'",(err,row)=>{
                if(row){
                    this.db.run(`UPDATE note set text= '${text}' where id = '${note_id}'`,(err)=>{
                        if(err){
                            console.log('无法插入内容',err)
                        }
                        callback(err)
                    })
                }else{
                    callback("not row")
                }
            })
        })
    }
    note_set(note_id,notebook_id,name,callback=(err)=>{}){
        var now = Date.now()
        this.db.serialize(()=>{
            this.db.get("SELECT id FROM note WHERE id ='"+note_id+"'",(err,row)=>{
                if(!row){
                    this.db.run(`INSERT INTO note(id,notebook_id,name,update_time,add_time,text)values('${note_id}','${notebook_id}','${name}','${now}','${now}','')`,(err)=>{
                        if(err){
                            console.log('无法插入内容',err)
                        }
                        callback(err)
                    })
                }else{
                    this.db.run(`UPDATE note set notebook_id='${notebook_id}',name='${name}',update_time='${now}' where id='${note_id}'`,(err)=>{
                        if(err){
                            console.log('无法更新内容',err)
                        }
                        callback(err)
                    })
                }
            })
        })
    }
    note_rem(note_id,callback=(err)=>{}){
        this.db.serialize(()=>{
            this.db.run("DELETE FROM note WHERE id = '"+note_id+"'",callback)
        })
    }
    close(){
        this.db.close()
    }
}

module.exports={
    NodeBook
}

// var cls = new NodeBook("a.db",()=>{
//     cls.all_search("F",(r)=>{
//         console.log(JSON.stringify(r,null,4))
//     })
//     // cls.note_set(Date.now(),"1","笔记1","笔记内容",(r)=>{
//     //     cls.note_list("1",(list,len)=>{
//     //         console.log("note",list,len)
//     //     })
//     // })
// })
